package handle_db;

import java.security.interfaces.RSAKey;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import entities.Calendar;
import entities.Group;
import entities.State;
import entities.User;

public class User_DB_Connector {

	private DB_Connector db_Connector;
	private int resultSum;
	public int getResultSum() {
		return resultSum;
	}

	private ResultSet resultSet;

	private User usr;

	// login_info
	private String user_password;
	private int user_type;

	// basic_info
	private String user_id;
	private String user_name;
	private int user_sex;

	// more_info
	private String tag;
	private String[] tag_list;
	private ArrayList<Group> group_list;
	private int current_state_id;
	private State current_state;

	public User_DB_Connector() {
		db_Connector = new DB_Connector();
	}

	/**
	 * method get_User
	 * 
	 * @param user_number
	 *            (type int)
	 * @return usr(type User) find a user entity by user_number
	 */
	public User get_User(int user_number) {
		try {
			usr = new User();
			// get user_base_info
			String UBI_sql = "SELECT *FROM user_base_info WHERE "
					+ "user_number=" + user_number;
			resultSet = db_Connector.executeQuery(UBI_sql);

			while (resultSet.next()) {
				user_sex = resultSet.getInt("user_sex");
				user_name = resultSet.getString("user_name");
				System.out.println(user_sex);
				user_id = resultSet.getString("user_id");
				usr.setUser_sex(user_sex);
				usr.setUser_name(user_name);
				usr.setUser_id(user_id);
			}

			// get user_login_info
			String ULI_sql = "SELECT *FROM user_login_info "
					+ "WHERE user_number=" + user_number;
			resultSet = db_Connector.executeQuery(ULI_sql);

			while (resultSet.next()) {
				user_password = resultSet.getString("user_password");
				user_type = resultSet.getInt("user_type");
				usr.setUser_password(user_password);
				usr.setUser_type(user_type);
			}

			// get user_more_info
			String UMI_sql = "SELECT *FROM user_more_info "
					+ "WHERE user_more_info.user_number=" + user_number;
			resultSet = db_Connector.executeQuery(UMI_sql);
			

			while (resultSet.next()) {
				// get tags
				tag = resultSet.getString("tag");
				
				tag_list = tag.split(",");
				usr.setTag_list(tag_list);
				current_state_id = resultSet.getInt("current_state_id");

				
				// use News_DB_Connector to query user's current_state
				// -------------------- DONE
				String CS_sql = "SELECT *FROM news WHERE news.news_id="
						+ current_state_id;
				resultSet = db_Connector.executeQuery(CS_sql);
				while (resultSet.next()) {
					current_state.setTitle(resultSet.getString("news_title"));
					current_state.setNews_id(resultSet.getInt("news_id"));
					current_state.setNews_type(resultSet.getInt("news_type"));
				}
				usr.setCurrent_state(current_state);

				// friends group remain to query -------------- DONE
				String FG_sql = "SELECT *FROM friends "
						+ "WHERE friends.user_number = " + user_number
						+ " ORDER BY group_name"; // order by group
				resultSet = db_Connector.executeQuery(FG_sql);
				String group_name = null;
				int friends_number;
				int group_size = 0;
				Group friends_group = new Group();
				group_list = null;
				ArrayList<Integer> group_member = new ArrayList<Integer>();
				while (resultSet.next()) {
					String tmp = resultSet.getString("group_name");
					friends_number = resultSet.getInt("friends_id");
					// if the group_name has already exits,
					// then add friend's number directly;
					// otherwise, create a new group, with new group_name.
					if (!tmp.equals(group_name) ) {
						if (group_name != null) {
							group_list.add(friends_group);
						}
						group_name = tmp;
						friends_group = new Group();
						group_member = new ArrayList<Integer>();
						friends_group.setGroup_name(group_name);
						group_member.add(friends_number);
						friends_group.setGroup_member(group_member);
						friends_group.setGroup_size(group_size++);
					} else {
						group_member.add(friends_number);
						friends_group.setGroup_size(group_size++);
					}
					//group_name = resultSet.getString(FG_sql);
				}
				usr.setFriends(group_list);

//				ArrayList<Calendar> myCalendar = new ArrayList<Calendar>();
//				String calendar = "SELECT *FROM calendar WHERE user_number="+user_number;
//				resultSet = db_Connector.executeQuery(calendar);
//				while (resultSet.next()) {
//					Calendar c = new Calendar();
//					c.setDate(resultSet.getDate("date"));
//					c.setMy_arrangement(resultSet.getString("content"));
//					myCalendar.add(c);
//				}
//				usr.setMy_calendar(myCalendar);
			}
			// close DB
			db_Connector.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("DB query err!");
		}
		return usr;
	}

	/**
	 * method set_User
	 * 
	 * @param usr
	 *            update a user entity in DB
	 */
	void set_User(User usr) {
		// update user_base_info
		String UBI_sql = "UPDATE user_base_info SET user_sex="	+ usr.isUser_sex() + " , user_name='" + usr.getUser_name() + "' , user_id=" + usr.getUser_id() + " WHERE user_number=" + usr.getUser_number();
		System.out.println(usr.getUser_name());
		resultSum = db_Connector.executeUpdate(UBI_sql);
		System.out.println(usr.getTag_list()[1]);
		// update user_login_info
		String ULI_sql = "UPDATE user_login_info SET user_password="+usr.getUser_password() + " , user_type=" + usr.getUser_type()+" WHERE user_number=" + usr.getUser_number();
		resultSum = db_Connector.executeUpdate(ULI_sql);
		System.out.println(usr.getTag_list()[0]);
		// update user_more_info
		tag = null;
		for(int i=0;i<usr.getTag_list().length;i++){
			tag = tag+(usr.getTag_list()[i])+",";
		}
		if(usr.getCurrent_state()!=null){
			String UMI_sql = "UPDATE user_more_info SET tag='"+tag+"', current_state_id="+usr.getCurrent_state().getNews_id()+" WHERE user_number="+usr.getUser_number();
			resultSum = db_Connector.executeUpdate(UMI_sql);
		}
		
		String sql = "DELETE FROM friends WHERE user_number="+usr.getUser_number();
		resultSum = db_Connector.executeUpdate(sql);
		
		group_list = usr.getFriends();
		if(group_list!=null){
			for(Group g:group_list) {
				for(int member:g.getGroup_member()) {
					String add = "INSERT INTO friends SET user_number="+usr.getUser_number()+
								", friends_id="+member+", group_name='"+g.getGroup_name()+"'"+", priority=0";
					resultSum = db_Connector.executeUpdate(add);
				}
			}
		}
		
		
		// TODO calendar group UPDATE
		
	}

	/**
	 * method add_User
	 * 
	 * @param usr
	 *            add a new user entity to DB
	 */
	public void add_User(User usr) {
		String UBI_sql = "INSERT INTO user_base_info SET user_number="+usr.getUser_number();
		//System.out.println(usr.getUser_number());
		//int default_password = 123;
		String ULI_sql = "INSERT INTO user_login_info SET user_number="+usr.getUser_number()+", user_password="+usr.getUser_password();
		//System.out.println(usr.getUser_number());
		String UMI_sql = "INSERT INTO user_more_info SET user_number="+ usr.getUser_number();
		//System.out.println(usr.getUser_number());
		db_Connector.executeUpdate(UBI_sql);
		//db_Connector.close();
		//System.out.println(usr.getUser_number());
		//db_Connector = new DB_Connector();
		db_Connector.executeUpdate(ULI_sql);
		db_Connector.executeUpdate(UMI_sql);
		//db_Connector.close();
		this.set_User(usr);
	}

	/**
	 * method delete_User
	 * 
	 * @param user_number
	 *            delete a user entity from DB
	 */
	public void delete_User(int user_number) {
		String UBI_sql = "DELETE FROM user_base_info WHERE user_number="
				+ user_number;
		String ULI_sql = "DELETE FROM user_login_info WHERE user_number="
				+ user_number;
		String UMI_sql = "DELETE FROM user_more_info WHERE user_number="
				+ user_number;

		db_Connector.executeUpdate(UBI_sql);
		db_Connector.executeUpdate(ULI_sql);
		db_Connector.executeUpdate(UMI_sql);
	}
	
	public void close() {
		db_Connector.close();
	}

	public ArrayList<User> search_User(String user_name2) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "SELECT user_number FROM user_base_info WHERE user_name='"+user_name2+"'";
		int user_number;
		ArrayList<User> user_list = new ArrayList<User>();
		resultSet = db_Connector.executeQuery(sql);
		while(resultSet.next()){
			user_number = resultSet.getInt("user_number");
			user_list.add(get_User(user_number));
		}
		return user_list;
	}
}
